using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;
using System.Collections.Generic;

/// <summary>
/// Summary description for ArticleDAO
/// </summary>
public class ArticleDAO : DBAccess
{
    #region sql Commands
    //string SQL_SELECT_ALL = "select * from Article";
    string SQL_SELECT_BY_ID = "select * from Article where ID=@id";
    string SQL_INSERT = "insert into Article(Title,Content,Tag,ModifiedDate,ModifiedBy, State)values(@Title,@Content,@Tag,@ModifiedDate,@ModifiedBy, @State)";
    string SQL_UPDATE = "update Article set Title = @Title, Content = @Content,Tag=@Tag,ModifiedDate=@ModifiedDate,ModifiedBy=@ModifiedBy, State=@State where ID = @id";
    string SQL_DELETE = "delete from Article where ID = @id";
    #endregion

    #region fields
    string message = "";
    #endregion

    #region constructors
    public ArticleDAO()
    {

    }
    #endregion

    #region properties
    public string Message
    {
        get { return message; }
        set { message = value; }
    }
    #endregion

    #region Methos
    /// <summary>
    /// Get article by id
    /// </summary>
    /// <param name="id"></param>
    /// <returns></returns>
    public Article GetArticle(int id)
    {
        Article art = new Article();
        try
        {

            SqlCommand command = new SqlCommand(SQL_SELECT_BY_ID, conn);
            if (Connect())
            {
                command.Parameters.AddWithValue("@id", id);
                SqlDataReader dr = command.ExecuteReader();
                while (dr.Read())
                {
                    art.Id = dr.GetInt32(0);
                    art.Title = dr.GetString(1);
                    art.Content = dr.GetString(2);
                    art.Tag = dr.GetString(3);
                    art.ModifiedDate = dr.GetDateTime(4);
                    art.ModifiedBy = dr.GetInt32(5);
                    art.State = dr.GetBoolean(6);
                }
            }
        }
        catch (SqlException ex) { message = ex.Message; }
        catch (Exception ex) { message = ex.Message; }
        return art;
    }
    /// <summary>
    /// Get all articles 
    /// </summary>
    /// <returns></returns>
    public IList<Article> GetAllArticles(int pageSize, int currentPage, ref int total,byte state)
    {
        IList<Article> list = new List<Article>();
        try
        {
            SqlCommand command = new SqlCommand("sp_SelectArticles", conn);
            command.CommandType = CommandType.StoredProcedure;
            command.Parameters.Add("@pagesize", SqlDbType.Int);
            command.Parameters.Add("@currentpage", SqlDbType.Int);
            command.Parameters.Add("@state", SqlDbType.TinyInt);
            command.Parameters.Add("@totals", SqlDbType.Int);
            command.Parameters["@pagesize"].Value = pageSize;
            command.Parameters["@currentpage"].Value = currentPage;
            command.Parameters["@state"].Value = state;
            command.Parameters["@totals"].Direction = ParameterDirection.Output;
            command.Parameters["@totals"].Value = total;
            if (Connect())
            {
                SqlDataReader dr = command.ExecuteReader();

                while (dr.Read())
                {
                    Article art = new Article();

                    art.Id = dr.GetInt32(1);
                    if(!dr.IsDBNull(2))art.Title = dr.GetString(2);                
                    if (!dr.IsDBNull(3)) art.Tag = dr.GetString(3);
                    art.State = dr.GetBoolean(4);
                    art.ModifiedDate = dr.GetDateTime(5);
                    art.ModifiedBy = dr.GetInt32(6);                 
                    list.Add(art);
                }
                dr.Close();
                total = int.Parse(command.Parameters["@totals"].Value.ToString());
                Disconnect();
            }
        }
        catch (SqlException ex) { message = ex.Message; }
        catch (Exception ex) { message = ex.Message; }
        return list;
    }
    /// <summary>
    /// 
    /// </summary>
    /// <param name="pageSize">number of elements per page</param>
    /// <param name="currentPage">current page to view</param>
    /// <param name="total">total record (out parameter)</param>
    /// <param name="keyword">keyword to search, which is text may appear in title, tag or content of the article</param>
    /// <returns>list of article</returns>
    public IList<Article> GetAllArticlesExt(int pageSize, int currentPage, ref int total, string keyword, byte state)
    {
        IList<Article> list = new List<Article>();
        try
        {
            SqlCommand command = new SqlCommand("sp_SelectArticlesExt", conn);
            command.CommandType = CommandType.StoredProcedure;
            command.Parameters.Add("@pagesize", SqlDbType.Int);
            command.Parameters.Add("@currentpage", SqlDbType.Int);
            command.Parameters.Add("@keyword", SqlDbType.NVarChar);
            command.Parameters.Add("@state", SqlDbType.TinyInt);
            command.Parameters.Add("@totals", SqlDbType.Int);
            command.Parameters["@pagesize"].Value = pageSize;
            command.Parameters["@currentpage"].Value = currentPage;
            command.Parameters["@keyword"].Value=keyword;
            command.Parameters["@state"].Value = state;
            command.Parameters["@totals"].Direction = ParameterDirection.Output;
            command.Parameters["@totals"].Value = total;
            if (Connect())
            {
                SqlDataReader dr = command.ExecuteReader();
                while (dr.Read())
                {
                    Article art = new Article();

                    art.Id = dr.GetInt32(1);
                    if (!dr.IsDBNull(2)) art.Title = dr.GetString(2);
                    if (!dr.IsDBNull(3)) art.Tag = dr.GetString(3);
                    art.State = dr.GetBoolean(4);
                    art.ModifiedDate = dr.GetDateTime(5);
                    art.ModifiedBy = dr.GetInt32(6);
                    list.Add(art);
                }
                dr.Close();
                total = int.Parse(command.Parameters["@totals"].Value.ToString());
                Disconnect();
            }
        }
        catch (SqlException ex) { message = ex.Message; }
        catch (Exception ex) { message = ex.Message; }
        return list;
    }
    /// <summary>
    /// Insert new article into database
    /// </summary>
    /// <param name="tour"></param>
    /// <returns></returns>
    public int Insert(Article art)
    {
        int effectedRows = 0;
        try
        {
            SqlCommand command = new SqlCommand(SQL_INSERT, conn);
            command.Parameters.AddWithValue("@Title", art.Title);
            command.Parameters.AddWithValue("@Content", art.Content);
            command.Parameters.AddWithValue("@Tag", art.Tag);
            command.Parameters.AddWithValue("@ModifiedDate", art.ModifiedDate);
            command.Parameters.AddWithValue("@ModifiedBy", art.ModifiedBy);
            command.Parameters.AddWithValue("@State", art.State);
            if (Connect())
            {
                effectedRows = command.ExecuteNonQuery();
                command.CommandText = "SELECT @@IDENTITY from Article";
                art.Id = int.Parse(command.ExecuteScalar().ToString());
                Disconnect();
            }
        }
        catch (SqlException ex) { message = ex.Message; }
        catch (Exception ex) { message = ex.Message; }
        return effectedRows;
    }

    /// <summary>
    /// Update article
    /// </summary>
    /// <param name="tour"></param>
    /// <returns></returns>
    public int Update(Article art)
    {
        int effectedRows = 0;
        try
        {
            SqlCommand command = new SqlCommand(SQL_UPDATE, conn);
            command.Parameters.AddWithValue("@Title", art.Title);
            command.Parameters.AddWithValue("@Content", art.Content);
            command.Parameters.AddWithValue("@Tag", art.Tag);
            command.Parameters.AddWithValue("@ModifiedDate", art.ModifiedDate);
            command.Parameters.AddWithValue("@ModifiedBy", art.ModifiedBy);
            command.Parameters.AddWithValue("@State", art.State);
            command.Parameters.AddWithValue("@id", art.Id);
            if (Connect())
            {
                effectedRows = command.ExecuteNonQuery();
                Disconnect();
            }
        }
        catch (SqlException ex) { message = ex.Message; }
        catch (Exception ex) { message = ex.Message; }
        return effectedRows;
    }

    /// <summary>
    /// Remove article out of database
    /// </summary>
    /// <param name="tour"></param>
    /// <returns></returns>
    public int Delete(int  Id)
    {
        int effectedRows = 0;
        try
        {
            SqlCommand command = new SqlCommand(SQL_DELETE, conn);
            command.Parameters.AddWithValue("@id", Id);
            if (Connect())
            {
                effectedRows = command.ExecuteNonQuery();
                Disconnect();
            }
        }
        catch (SqlException ex) { message = ex.Message; }
        catch (Exception ex) { message = ex.Message; }
        return effectedRows;
    }

    /// <summary>
    /// Get list of articles with paging
    /// </summary>
    /// <param name="currentPage">Current page to view</param>
    /// <param name="pageSize">Number of element to be loaded in a page</param>
    /// <param name="total">total result</param>
    /// <returns>IList&lt;Artilce&gt;</returns>
    public IList<Article> GetArticles(int currentPage, int pageSize, ref int total, string keyword, byte state)
    {
        IList<Article> list = new List<Article>();
        try
        {
            SqlCommand command = new SqlCommand("sp_SelectArticles", conn);
            command.CommandType = CommandType.StoredProcedure;
            command.Parameters.Add("@pagesize", SqlDbType.Int);
            command.Parameters.Add("@currentpage", SqlDbType.Int);
            command.Parameters.Add("@keyword",SqlDbType.NVarChar);
            command.Parameters.Add("@state", SqlDbType.TinyInt);
            command.Parameters.Add("@totals", SqlDbType.Int);
            command.Parameters["@pagesize"].Value = pageSize;
            command.Parameters["@currentpage"].Value = currentPage;
            command.Parameters["@keyword"].Value = keyword;
            command.Parameters["@state"].Value = state;
            command.Parameters["@totals"].Direction = ParameterDirection.Output;
            command.Parameters["@totals"].Value = total;
            if (Connect())
            {
                SqlDataReader dr = command.ExecuteReader();
                while (dr.Read())
                {
                    Article art = new Article();
                    art.Id = dr.GetInt32(1);
                    art.Title = dr.GetString(2);                    
                    art.Tag = dr.GetString(3);
                    art.State = dr.GetBoolean(4);
                    art.ModifiedDate = dr.GetDateTime(5);
                    art.ModifiedBy = dr.GetInt32(6);
                    list.Add(art);
                }
                dr.Close();
                total = int.Parse(command.Parameters["@totals"].Value.ToString());
            }
        }
        catch (SqlException ex) { message = ex.Message; }
        catch (Exception ex) { message = ex.Message; }
        return list;
    }

    #endregion
}